#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "

-- 插入数据到打卡明细表
-- 主表
    -- dim_tbh_class_time_table
-- 其他表作为从表 left join
insert into hive.edu_online_dwb.dwb_signin_detail
select
    -- 班级课表
    course.class_id,
    course.content,
    -- 学生打卡记录表
    sign.student_id,
    sign.time_table_id,
    sign.signin_time,
    sign.signin_date,
    sign.share_state,
    -- 班级作息时间表
    t.morning_begin_time,
    t.morning_end_time,
    t.afternoon_begin_time,
    t.afternoon_end_time,
    t.evening_begin_time,
    t.evening_end_time,
    t.use_begin_date,
    t.use_end_date,

    course.class_date
from hive.edu_online_dwd.dim_course_table_upload_detail course
left join hive.edu_online_dwd.dim_tbh_class_time_table t
    on t.class_id = course.class_id and
        course.class_date >= t.use_begin_date and course.class_date <= t.use_end_date
left join hive.edu_online_dwd.fact_tbh_student_signin_record sign
    on course.class_id = sign.class_id and
        course.class_date = sign.signin_date
where course.content is not null and sign.share_state = 1; -- 筛掉空值及为共屏的数据

-- 插入数据到请假明细表
-- 主表
    -- dim_tbh_class_time_table
-- 其他表作为从表 left join
insert into hive.edu_online_dwb.dwb_leave_detail
select
    -- 班级课表
    course.class_id,
    course.content,
    morning_begin_time,
    morning_end_time,
    afternoon_begin_time,
    afternoon_end_time,
    evening_begin_time,
    evening_end_time,
    use_begin_date,
    use_end_date,
    -- 学生请假申请表
    student_id,
    audit_state,
    cancel_state,
    valid_state,
    begin_time,
    end_time,

    course.class_date
from hive.edu_online_dwd.dim_course_table_upload_detail course
left join hive.edu_online_dwd.dim_tbh_class_time_table t
    on t.class_id = course.class_id and
        course.class_date >= t.use_begin_date and course.class_date <= t.use_end_date
left join hive.edu_online_dwd.fact_student_leave_apply leave
    on course.class_id = leave.class_id
where course.content is not null and
    -- 有效请假
    audit_state = 1 and
    cancel_state = 0 and
    valid_state = 1;
"

